package com.yupi.springbootinit.service.impl;

import cn.hutool.core.io.FileUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yupi.springbootinit.common.ErrorCode;
import com.yupi.springbootinit.constant.CommonConstant;
import com.yupi.springbootinit.exception.BusinessException;
import com.yupi.springbootinit.listener.ExcelListener;
import com.yupi.springbootinit.manager.AIManager;
import com.yupi.springbootinit.manager.CosManager;
import com.yupi.springbootinit.model.dto.chart.ChartQueryRequest;
import com.yupi.springbootinit.model.entity.Chart;
import com.yupi.springbootinit.service.ChartService;
import com.yupi.springbootinit.mapper.ChartMapper;
import com.yupi.springbootinit.utils.ExcelUtils;
import com.yupi.springbootinit.utils.SqlUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ThreadPoolExecutor;

/**
* @author 38034
* @description 针对表【chart(图表信息表)】的数据库操作Service实现
* @createDate 2024-09-21 16:19:49
*/
@Service
public class ChartServiceImpl extends ServiceImpl<ChartMapper, Chart>
    implements ChartService{

    @Resource
    private ChartMapper chartMapper;

    @Resource
    private AIManager aiManager;

    @Resource
    private ThreadPoolExecutor threadPoolExecutor;

    @Resource
    private CosManager cosManager;
    /**
     * 获取查询包装类
     *
     * @param chartQueryRequest
     * @return
     */
    public QueryWrapper<Chart> getQueryWrapper(ChartQueryRequest chartQueryRequest) {
        QueryWrapper<Chart> queryWrapper = new QueryWrapper<>();
        if (chartQueryRequest == null) {
            return queryWrapper;
        }

        String name = chartQueryRequest.getName();
        String goal = chartQueryRequest.getGoal();
        String chartType = chartQueryRequest.getChartType();
        Long userId = chartQueryRequest.getUserId();

        String sortField = chartQueryRequest.getSortField();
        String sortOrder = chartQueryRequest.getSortOrder();

        // 拼接查询条件
        queryWrapper.like(StringUtils.isNotBlank(name),"name",name);
        queryWrapper.like(StringUtils.isNotBlank(goal),"goal",goal);
        queryWrapper.eq(userId!=null,"userId",userId);
        queryWrapper.eq(StringUtils.isNotBlank(chartType),"chartTypr",chartType);
        queryWrapper.orderBy(SqlUtils.validSortField(sortField), sortOrder.equals(CommonConstant.SORT_ORDER_ASC),sortField);
        return queryWrapper;
    }

    @Override
    public void createDefineChartTable(Long chartId, ExcelListener excelListener) {
        Map headerMap = excelListener.getMap();

        //创建建表语句
        StringBuilder creatTableSql = new StringBuilder("create table if not exists ");
        //表名
        String tableName = "chart_"+chartId;
        creatTableSql.append(tableName).append("(");

        Iterator iterator = headerMap.values().iterator();
        while (iterator.hasNext()){
            creatTableSql.append(iterator.next()).append(" varchar(128) null");
            if(iterator.hasNext()){
                creatTableSql.append(",");
            }
            creatTableSql.append("\n");
        }
        creatTableSql.append(");");
        String s = creatTableSql.toString();
        chartMapper.createTable(s);

    }

    @Override
    public void insertDefineData(Long chartId, ExcelListener excelListener) {
        //表名
        String tableName = "chart_"+chartId;
        Map headerMap = excelListener.getMap();
        List<Map<String, Object>> cachedDataList = excelListener.getCachedDataList();

        StringBuilder insertSql = new StringBuilder("insert into "+tableName+" (");
        Iterator iterator = headerMap.values().iterator();
        while (iterator.hasNext()){
            Object next = iterator.next();
            insertSql.append(next);
            if(iterator.hasNext()){
                insertSql.append(",");
            }
        }
        insertSql.append(") values ");

        Iterator<Map<String, Object>> iterator1 = cachedDataList.iterator();
        while (iterator1.hasNext()){
            Map<String, Object> next = iterator1.next();
            insertSql.append("(");
            Iterator<Object> iterator2 = next.values().iterator();
            while (iterator2.hasNext()){
                Object next1 = iterator2.next();
                insertSql.append("'"+next1+"'");
                if(iterator2.hasNext()){
                    insertSql.append(",");
                }
            }
            insertSql.append(")");
            if(iterator1.hasNext()){
                insertSql.append(",");
            }
        }


        chartMapper.insertData(insertSql.toString());
    }

    @Override
    public List<Map<String, Object>> getChartDataById(long id) {
        String chartName = "chart_"+id;
        String querySql = "select * from "+chartName;
        return chartMapper.queryChartData(querySql);
    }

    @Override
    public void sycnGenChartByAi(Chart chart,File tempFile){

        Long userId = chart.getUserId();

        //异步读取不到文件，采取同步读取的方式
        // 将ExcelListener从ExcelUtils.excelToCsv（）方法中提取出来，这里需要用到excelListener对象存储的数组
        ExcelListener excelListener = new ExcelListener();//监听器类，用来提取监听到的数据与表头
        String csvData = ExcelUtils.excelToCsv(tempFile, excelListener);

        //todo 建议处理任务队列满了后，抛异常的情况
        File finalTempFile = tempFile;
        CompletableFuture.runAsync(() -> {
            //创建一个新的表chart_id
            Long chartId = chart.getId();

            //修改任务状态为执行中
            Chart updateChart = new Chart();
            updateChart.setId(chartId);
            //todo 建议定义状态为枚举
            updateChart.setStatus("running");
            boolean b = updateById(updateChart);
            if (!b) {
                handleChartUpdate(chartId,"更新图表状态失败", finalTempFile,userId);
                return;
            }


            //调用接口

            //生成提问语句
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("你是一个数据分析师，接下来我会给你我的分析目标和原始数据，请告诉我分析结论。\n");
            stringBuilder.append("生成目标：" + chart.getGoal()).append("\n");
            stringBuilder.append("数据：" + csvData).append("\n");

            //调用Ai接口，返回字符串
            String biResponseStr = aiManager.sendMsgToXingHuo(true, stringBuilder.toString());
            String[] split = biResponseStr.split("'【【【【【'");
            if (split.length < 3) {
                handleChartUpdate(chartId,"AI生成错误", finalTempFile,userId);
                return;
            }
            String genChart = split[1].trim();
            String genResult = split[2].trim();
            updateChart.setStatus("succeed");
            updateChart.setGenChart(genChart);
            updateChart.setGenResult(genResult);
            boolean updateResult = updateById(updateChart);
            if(!updateResult){
                handleChartUpdate(chartId,"更新图表状态失败", finalTempFile,userId);
            }

            //自定义创建一个新的表
            createDefineChartTable(chartId, excelListener);
            //插入数据
            insertDefineData(chartId, excelListener);

        },threadPoolExecutor);

    }

    @Override
    public void handleChartUpdate(long chartId, String execMessage, File file, Long userId){
        Chart updateChart = new Chart();
        updateChart.setId(chartId);
        updateChart.setStatus("failed");
        updateChart.setExecMessage(execMessage);
        boolean b = updateById(updateChart);
        if(!b){
            log.error("更新图表状态失败:"+chartId+","+execMessage);
        }else {
            // 文件目录：根据业务、用户来划分
            //上传文件
            //文件路径：/方法名/用户id/图表id.excel后缀
            String suffix = FileUtil.getSuffix(file.getName());
            String filepath = String.format("/%s/%s/%s.%s","genChartByAi",userId,"chart_"+chartId,suffix);
            try {
                // 上传文件
                cosManager.putObject(filepath, file);
            } catch (Exception e) {
                log.error("file upload error, filepath = " + filepath, e);
                throw new BusinessException(ErrorCode.SYSTEM_ERROR, "上传失败");
            } finally {
                if (file != null) {
                    // 删除临时文件
                    boolean delete = file.delete();
                    if (!delete) {
                        log.error("file delete error, filepath = {}");
                    }
                }
            }

        }

    }
}




